----CREATE DATABASE----
CREATE DATABASE TOURS
GO
USE TOURS
GO

----CREATE TABLE----
--TABLE: Tours
CREATE TABLE Tours
(
	tourID int identity (1,1) primary key,
	tourCode char(5) not null,
	tourName varchar(50) not null,
	race int,
	placeStart varchar(30) not null,
	placeEnd varchar(30) not null,
	startDate datetime not null,
	endDate datetime,
	intervalTime float not null, --ex: 2,5 day; 3 day
	price money not null,
	sellPrice money,
	itinerary varchar(100), --ex: HaNoi->DaNang->HaNoi
	planTour varchar(MAX),
	status bit not null default 0 --ex: 0: can order, 1: can't order
)
GO

--TABLE: Hotels
CREATE TABLE Hotels
(
	hotelID int identity (1,1) primary key,
	hotelName varchar(50) not null,
	price money not null,
	star int,
	room int not null,
	address varchar(100) not null,
	phone varchar(20) not null,
	hotelDescription varchar(max)
)
GO

--TABLE: Tour-Hotel
CREATE TABLE Tour_Hotel
(
	tourHotelID int identity(1,1) primary key,
	tourID int not null foreign key references Tours(tourID),
	hotelID int not null foreign key references Hotels(hotelID),
	numRoom int,
	departDate datetime not null,
	returnDate datetime not null,
	intervalTime float
)
GO

--TABLE: Landscapes
CREATE TABLE Landscapes
(
	landscapeID int identity (1,1) primary key,
	landscapeName varchar(50) not null,
	race int,
	landscapeDescription varchar(MAX)
)
GO

--TABLE: Tour-Landscape
--CREATE TABLE Tour_Landscape
--(
--	tourID int not null foreign key references Tours(tourID),
--	landscapeID int not null foreign key references Landscapes(landscapeID),
--	constraint pk_tour_hotel primary key(tourID,landscapeID),
--)
GO

--TABLE: Customers
CREATE TABLE Customers
(
	cusID int identity(1,1) primary key,
	cusName varchar(40) not null,
	tel varchar(20) not null,
	email varchar(50)
)
GO

--TABLE: OrderTour
CREATE TABLE OrderTour
(
	orderID int identity(1,1) primary key,
	tourID int not null foreign key references Tours(tourID),
	cusID int not null foreign key references Customers(cusID),
	numAdult int,
	numChild int,
	regDate datetime not null,
	status bit default 0
)

--TABLE: Images
CREATE TABLE Images
(
	imageID int identity(1,1) primary key,
	pathLink varchar(100) not null,
	type varchar(1), --ex: 1: images of tour, 2: images of hotel, 3:images of Landscape
	foreignID int not null --ex: 1: foreign of tour, 2 of hotel, 3 of landscape
)
GO

--TABLE: Admin
CREATE TABLE Admin
(
	adminID int identity(1,1) primary key,
	adminName varchar(40) not null,
	username varchar(30) not null,
	pass varchar(40) not null,
	email varchar(50),
	tel varchar(20)
)
GO
INSERT INTO Admin VALUES('Administrator','admin','!#/)zW??C?JJ??','','');